{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "bc059d9b-032f-4b9a-b463-efa7b3beb0db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgresql://hbu:********@127.0.0.1:2345/hbu\n",
      "环境变量加载完成！\n",
      "数据库连接引擎创建成功！\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 73 entries, 0 to 72\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype         \n",
      "---  ------  --------------  -----         \n",
      " 0   symbol  73 non-null     object        \n",
      " 1   债券简称    73 non-null     object        \n",
      " 2   申购日期    73 non-null     datetime64[ns]\n",
      " 3   上市时间    71 non-null     datetime64[ns]\n",
      "dtypes: datetime64[ns](2), object(2)\n",
      "memory usage: 2.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2\n",
    "from dotenv import load_dotenv\n",
    "\n",
    "# 加载环境变量\n",
    "load_dotenv()\n",
    "\n",
    "# 从环境变量获取数据库连接信息，如果不存在则使用默认值\n",
    "driver=os.getenv('DRIVER', 'postgresql')\n",
    "user=os.getenv('PGUSER', None)\n",
    "password=os.getenv('PGPASSWORD', None)\n",
    "host=os.getenv('PGHOST', None)\n",
    "port=os.getenv('PGPORT', None)\n",
    "database=os.getenv('PGDATABASE', 'postgres')\n",
    "\n",
    "# schema is used for postgres, similiar with database level in MySQL\n",
    "schema=os.getenv('SCHEMA',\"public\")\n",
    "\n",
    "DATABASE_URL = f\"{driver}://{user}:{password}@{host}:{port}/{database}\"\n",
    "\n",
    "if user is not None and password is not None:\n",
    "    print(f\"{driver}://{user}:********@{host}:{port}/{database}\")\n",
    "else:\n",
    "    print('非法的数据库连接URL')\n",
    "    sys.exit(1)\n",
    "print('环境变量加载完成！')\n",
    "\n",
    "# 创建数据库连接引擎\n",
    "try:\n",
    "    engine = create_engine(DATABASE_URL)\n",
    "    print('数据库连接引擎创建成功！')\n",
    "except Exception as e:\n",
    "    print(f'创建数据库连接引擎失败: {e}')\n",
    "    raise\n",
    "# 从PostgreSQL数据库读取数据\n",
    "\n",
    "query = \"\"\"\n",
    "    SELECT \n",
    "        symbol,\n",
    "        \"债券简称\",\n",
    "        \"申购日期\",\n",
    "        \"上市时间\"\n",
    "    FROM bond_zh_cov_base\n",
    "    WHERE \n",
    "        \"申购日期\" >= '2024-01-01'\n",
    "    ORDER BY \"申购日期\"\n",
    "    \"\"\"\n",
    "df = pd.read_sql(query, engine)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "bb85d70a-72bf-464a-ae46-609e11336dec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>债券简称</th>\n",
       "      <th>申购日期</th>\n",
       "      <th>上市时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sz127103</td>\n",
       "      <td>东南转债</td>\n",
       "      <td>2024-01-03</td>\n",
       "      <td>2024-01-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sz123237</td>\n",
       "      <td>佳禾转债</td>\n",
       "      <td>2024-01-04</td>\n",
       "      <td>2024-01-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sz123238</td>\n",
       "      <td>卡倍转02</td>\n",
       "      <td>2024-01-11</td>\n",
       "      <td>2024-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sz123239</td>\n",
       "      <td>锋工转债</td>\n",
       "      <td>2024-01-19</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sz127104</td>\n",
       "      <td>姚记转债</td>\n",
       "      <td>2024-01-25</td>\n",
       "      <td>2024-02-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     symbol   债券简称       申购日期       上市时间\n",
       "0  sz127103   东南转债 2024-01-03 2024-01-24\n",
       "1  sz123237   佳禾转债 2024-01-04 2024-01-24\n",
       "2  sz123238  卡倍转02 2024-01-11 2024-02-01\n",
       "3  sz123239   锋工转债 2024-01-19 2024-02-22\n",
       "4  sz127104   姚记转债 2024-01-25 2024-02-26"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d9457412-d116-4630-ae1e-b6313759fe97",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "正在处理第 1 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 东南转债(sz127103) 的历史数据，共 430 行\n",
      "  ✓ 成功获取 佳禾转债(sz123237) 的历史数据，共 430 行\n",
      "  ✓ 成功获取 卡倍转02(sz123238) 的历史数据，共 226 行\n",
      "  ✓ 成功获取 锋工转债(sz123239) 的历史数据，共 415 行\n",
      "  ✓ 成功获取 姚记转债(sz127104) 的历史数据，共 413 行\n",
      "\n",
      "数据获取完成！总共获取 1914 行交易数据\n"
     ]
    }
   ],
   "source": [
    "import akshare as ak\n",
    "import pandas as pd\n",
    "import time\n",
    "from tqdm import tqdm  # 进度条工具，可选\n",
    "\n",
    "def get_cov_batch_history(symbol_list, name_list, batch_size=5, delay=1):\n",
    "    \"\"\"\n",
    "    分批次获取可转债历史数据\n",
    "    \n",
    "    Parameters:\n",
    "    - symbol_list: 可转债代码列表\n",
    "    - name_list: 可转债简称列表  \n",
    "    - batch_size: 每批数量，默认为5\n",
    "    - delay: 每次请求间隔，避免频繁请求\n",
    "    \"\"\"\n",
    "    all_data = []\n",
    "    \n",
    "    # 按批次处理\n",
    "    for i in range(0, len(symbol_list), batch_size):\n",
    "        batch_symbols = symbol_list[i:i + batch_size]\n",
    "        batch_names = name_list[i:i + batch_size]\n",
    "        \n",
    "        print(f\"正在处理第 {i//batch_size + 1} 批数据，共 {len(batch_symbols)} 个可转债...\")\n",
    "        \n",
    "        # 处理当前批次中的每个可转债\n",
    "        for symbol, bond_name in zip(batch_symbols, batch_names):\n",
    "            try:\n",
    "                # 获取单个可转债的历史数据\n",
    "                bond_data = ak.bond_zh_hs_cov_daily(symbol=symbol)\n",
    "                \n",
    "                # 添加标识列\n",
    "                bond_data['symbol'] = symbol\n",
    "                bond_data['债券简称'] = bond_name\n",
    "                \n",
    "                # 重置索引，将日期变为列\n",
    "                if 'date' in bond_data.columns:\n",
    "                    bond_data.reset_index(inplace=True)\n",
    "                \n",
    "                all_data.append(bond_data)\n",
    "                print(f\"  ✓ 成功获取 {bond_name}({symbol}) 的历史数据，共 {len(bond_data)} 行\")\n",
    "                \n",
    "            except Exception as e:\n",
    "                print(f\"  ✗ 获取 {bond_name}({symbol}) 数据失败: {str(e)}\")\n",
    "                continue\n",
    "            \n",
    "            # 添加延迟，避免请求过于频繁\n",
    "            time.sleep(delay)\n",
    "    \n",
    "    # 合并所有数据\n",
    "    if all_data:\n",
    "        final_df = pd.concat(all_data, ignore_index=True)\n",
    "        print(f\"\\n数据获取完成！总共获取 {len(final_df)} 行交易数据\")\n",
    "        return final_df\n",
    "    else:\n",
    "        print(\"未获取到任何数据\")\n",
    "        return pd.DataFrame()\n",
    "\n",
    "\n",
    "\n",
    "# 调用df.head()历史记录\n",
    "df_sample=df.head().copy()\n",
    "result_df = get_cov_batch_history(\n",
    "        symbol_list=df_sample['symbol'].tolist(),\n",
    "        name_list=df_sample['债券简称'].tolist(),\n",
    "        batch_size=5,  # 每批5个\n",
    "        delay=1        # 每次请求间隔1秒\n",
    "    )\n",
    "    \n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d2e214b4-03a1-4517-b8a9-ba41f41d9719",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>symbol</th>\n",
       "      <th>债券简称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2024-01-24</td>\n",
       "      <td>109.010</td>\n",
       "      <td>109.999</td>\n",
       "      <td>104.207</td>\n",
       "      <td>105.000</td>\n",
       "      <td>14362920</td>\n",
       "      <td>sz127103</td>\n",
       "      <td>东南转债</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2024-01-25</td>\n",
       "      <td>105.300</td>\n",
       "      <td>109.950</td>\n",
       "      <td>105.300</td>\n",
       "      <td>107.900</td>\n",
       "      <td>6639941</td>\n",
       "      <td>sz127103</td>\n",
       "      <td>东南转债</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2024-01-26</td>\n",
       "      <td>108.201</td>\n",
       "      <td>109.990</td>\n",
       "      <td>107.701</td>\n",
       "      <td>109.036</td>\n",
       "      <td>2774475</td>\n",
       "      <td>sz127103</td>\n",
       "      <td>东南转债</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2024-01-29</td>\n",
       "      <td>109.035</td>\n",
       "      <td>109.520</td>\n",
       "      <td>107.208</td>\n",
       "      <td>107.392</td>\n",
       "      <td>962845</td>\n",
       "      <td>sz127103</td>\n",
       "      <td>东南转债</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2024-01-30</td>\n",
       "      <td>107.262</td>\n",
       "      <td>108.595</td>\n",
       "      <td>106.560</td>\n",
       "      <td>106.800</td>\n",
       "      <td>939308</td>\n",
       "      <td>sz127103</td>\n",
       "      <td>东南转债</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index        date     open     high  ...    close    volume    symbol  债券简称\n",
       "0      0  2024-01-24  109.010  109.999  ...  105.000  14362920  sz127103  东南转债\n",
       "1      1  2024-01-25  105.300  109.950  ...  107.900   6639941  sz127103  东南转债\n",
       "2      2  2024-01-26  108.201  109.990  ...  109.036   2774475  sz127103  东南转债\n",
       "3      3  2024-01-29  109.035  109.520  ...  107.392    962845  sz127103  东南转债\n",
       "4      4  2024-01-30  107.262  108.595  ...  106.800    939308  sz127103  东南转债\n",
       "\n",
       "[5 rows x 9 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b00176d1-cb34-4d8d-9258-0dde75380b7d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "正在处理第 1 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 东南转债(sz127103) 的历史数据，共 430 行\n",
      "  ✓ 成功获取 佳禾转债(sz123237) 的历史数据，共 430 行\n",
      "  ✓ 成功获取 卡倍转02(sz123238) 的历史数据，共 226 行\n",
      "  ✓ 成功获取 锋工转债(sz123239) 的历史数据，共 415 行\n",
      "  ✓ 成功获取 姚记转债(sz127104) 的历史数据，共 413 行\n",
      "正在处理第 2 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 楚天转债(sz123240) 的历史数据，共 410 行\n",
      "  ✓ 成功获取 龙星转债(sz127105) 的历史数据，共 406 行\n",
      "  ✓ 成功获取 益丰转债(sh113682) 的历史数据，共 392 行\n",
      "  ✓ 成功获取 伟24转债(sh113683) 的历史数据，共 376 行\n",
      "  ✓ 成功获取 湘泵转债(sh113684) 的历史数据，共 225 行\n",
      "正在处理第 3 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 宏柏转债(sh111019) 的历史数据，共 363 行\n",
      "  ✓ 成功获取 升24转债(sh113685) 的历史数据，共 318 行\n",
      "  ✓ 成功获取 泰瑞转债(sh113686) 的历史数据，共 313 行\n",
      "  ✓ 成功获取 利扬转债(sh118048) 的历史数据，共 316 行\n",
      "  ✓ 成功获取 欧通转债(sz123241) 的历史数据，共 310 行\n",
      "正在处理第 4 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 赛龙转债(sz123242) 的历史数据，共 309 行\n",
      "  ✓ 成功获取 严牌转债(sz123243) 的历史数据，共 310 行\n",
      "  ✓ 成功获取 振华转债(sh113687) 的历史数据，共 306 行\n",
      "  ✓ 成功获取 合顺转债(sh111020) 的历史数据，共 297 行\n",
      "  ✓ 成功获取 奥锐转债(sh111021) 的历史数据，共 297 行\n",
      "正在处理第 5 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 松原转债(sz123244) 的历史数据，共 266 行\n",
      "  ✓ 成功获取 汇成转债(sh118049) 的历史数据，共 285 行\n",
      "  ✓ 成功获取 豫光转债(sh110096) 的历史数据，共 284 行\n",
      "  ✓ 成功获取 伟隆转债(sz127106) 的历史数据，共 220 行\n",
      "  ✓ 成功获取 集智转债(sz123245) 的历史数据，共 287 行\n",
      "正在处理第 6 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 远信转债(sz123246) 的历史数据，共 283 行\n",
      "  ✓ 成功获取 万凯转债(sz123247) 的历史数据，共 281 行\n",
      "  ✓ 成功获取 恒辉转债(sz123248) 的历史数据，共 209 行\n",
      "  ✓ 成功获取 航宇转债(sh118050) 的历史数据，共 279 行\n",
      "  ✓ 成功获取 国检转债(sh113688) 的历史数据，共 241 行\n",
      "正在处理第 7 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 洛凯转债(sh113689) 的历史数据，共 242 行\n",
      "  ✓ 成功获取 豪24转债(sh113690) 的历史数据，共 212 行\n",
      "  ✓ 成功获取 英搏转债(sz123249) 的历史数据，共 241 行\n",
      "  ✓ 成功获取 天润转债(sh110097) 的历史数据，共 238 行\n",
      "  ✓ 成功获取 和邦转债(sh113691) 的历史数据，共 236 行\n",
      "正在处理第 8 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 保隆转债(sh113692) 的历史数据，共 230 行\n",
      "  ✓ 成功获取 嘉益转债(sz123250) 的历史数据，共 230 行\n",
      "  ✓ 成功获取 领益转债(sz127107) 的历史数据，共 202 行\n",
      "  ✓ 成功获取 皓元转债(sh118051) 的历史数据，共 214 行\n",
      "  ✓ 成功获取 华医转债(sz123251) 的历史数据，共 199 行\n",
      "正在处理第 9 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 南药转债(sh110098) 的历史数据，共 193 行\n",
      "  ✓ 成功获取 银邦转债(sz123252) 的历史数据，共 188 行\n",
      "  ✓ 成功获取 渝水转债(sh113070) 的历史数据，共 181 行\n",
      "  ✓ 成功获取 永贵转债(sz123253) 的历史数据，共 149 行\n",
      "  ✓ 成功获取 浩瀚转债(sh118052) 的历史数据，共 145 行\n",
      "正在处理第 10 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 正帆转债(sh118053) 的历史数据，共 146 行\n",
      "  ✓ 成功获取 志邦转债(sh113693) 的历史数据，共 144 行\n",
      "  ✓ 成功获取 亿纬转债(sz123254) 的历史数据，共 140 行\n",
      "  ✓ 成功获取 太能转债(sz127108) 的历史数据，共 137 行\n",
      "  ✓ 成功获取 鼎龙转债(sz123255) 的历史数据，共 132 行\n",
      "正在处理第 11 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 安集转债(sh118054) 的历史数据，共 131 行\n",
      "  ✓ 成功获取 清源转债(sh113694) 的历史数据，共 131 行\n",
      "  ✓ 成功获取 伟测转债(sh118055) 的历史数据，共 128 行\n",
      "  ✓ 成功获取 恒帅转债(sz123256) 的历史数据，共 97 行\n",
      "  ✓ 成功获取 路维转债(sh118056) 的历史数据，共 81 行\n",
      "正在处理第 12 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 电化转债(sz127109) 的历史数据，共 84 行\n",
      "  ✓ 成功获取 安克转债(sz123257) 的历史数据，共 84 行\n",
      "  ✓ 成功获取 锡振转债(sh111022) 的历史数据，共 79 行\n",
      "  ✓ 成功获取 华辰转债(sh113695) 的历史数据，共 81 行\n",
      "  ✓ 成功获取 甬矽转债(sh118057) 的历史数据，共 77 行\n",
      "正在处理第 13 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 伯25转债(sh113696) 的历史数据，共 69 行\n",
      "  ✓ 成功获取 利柏转债(sh111023) 的历史数据，共 73 行\n",
      "  ✓ 成功获取 广核转债(sz127110) 的历史数据，共 69 行\n",
      "  ✓ 成功获取 微导转债(sh118058) 的历史数据，共 47 行\n",
      "  ✓ 成功获取 凯众转债(sh113698) 的历史数据，共 37 行\n",
      "正在处理第 14 批数据，共 5 个可转债...\n",
      "  ✓ 成功获取 金威转债(sz127111) 的历史数据，共 38 行\n",
      "  ✓ 成功获取 胜蓝转02(sz123258) 的历史数据，共 36 行\n",
      "  ✓ 成功获取 应流转债(sh113697) 的历史数据，共 13 行\n",
      "  ✓ 成功获取 金25转债(sh113699) 的历史数据，共 10 行\n",
      "  ✓ 成功获取 福能转债(sh110099) 的历史数据，共 7 行\n",
      "正在处理第 15 批数据，共 3 个可转债...\n",
      "  ✓ 成功获取 锦浪转02(sz123259) 的历史数据，共 1 行\n",
      "  ✗ 获取 颀中转债(sh118059) 数据失败: 'date'\n",
      "  ✗ 获取 卓镁转债(sz123260) 数据失败: 'date'\n",
      "\n",
      "数据获取完成！总共获取 14669 行交易数据\n"
     ]
    }
   ],
   "source": [
    "data=get_cov_batch_history(\n",
    "        symbol_list=df['symbol'].tolist(),\n",
    "        name_list=df['债券简称'].tolist(),\n",
    "        batch_size=5,  # 每批5个\n",
    "        delay=2        # 每次请求间隔1秒\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d996ae87-200d-4cda-8c5d-171a5bf61168",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "669"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.to_sql(\n",
    "    name='bond_zh_hs_cov_daily',\n",
    "    con=engine,\n",
    "    if_exists='replace',\n",
    "    index=True\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "537e49fe-8656-4f98-8543-201a07ea0588",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14669"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f9b6a3fb-3dd6-4d74-b986-3490397021af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14669 entries, 0 to 14668\n",
      "Data columns (total 9 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   index   14669 non-null  int64  \n",
      " 1   date    14669 non-null  object \n",
      " 2   open    14669 non-null  float64\n",
      " 3   high    14669 non-null  float64\n",
      " 4   low     14669 non-null  float64\n",
      " 5   close   14669 non-null  float64\n",
      " 6   volume  14669 non-null  int64  \n",
      " 7   symbol  14669 non-null  object \n",
      " 8   债券简称    14669 non-null  object \n",
      "dtypes: float64(4), int64(2), object(3)\n",
      "memory usage: 1.0+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "837ae4d6-5941-44ee-829d-cad67ed20bfd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      symbol  债券简称       申购日期 上市时间\n",
      "71  sh118059  颀中转债 2025-11-03  NaT\n",
      "72  sz123260  卓镁转债 2025-11-07  NaT\n"
     ]
    }
   ],
   "source": [
    "print(df[df['上市时间'].isnull()])\n",
    "#df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "c03ecef8-c60e-4a93-b6b3-04760dcb85ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>债券简称</th>\n",
       "      <th>申购日期</th>\n",
       "      <th>上市时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sz127103</td>\n",
       "      <td>东南转债</td>\n",
       "      <td>2024-01-03</td>\n",
       "      <td>2024-01-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sz123237</td>\n",
       "      <td>佳禾转债</td>\n",
       "      <td>2024-01-04</td>\n",
       "      <td>2024-01-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sz123238</td>\n",
       "      <td>卡倍转02</td>\n",
       "      <td>2024-01-11</td>\n",
       "      <td>2024-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>sz123239</td>\n",
       "      <td>锋工转债</td>\n",
       "      <td>2024-01-19</td>\n",
       "      <td>2024-02-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sz127104</td>\n",
       "      <td>姚记转债</td>\n",
       "      <td>2024-01-25</td>\n",
       "      <td>2024-02-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>sz123258</td>\n",
       "      <td>胜蓝转02</td>\n",
       "      <td>2025-08-28</td>\n",
       "      <td>2025-09-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>sh113697</td>\n",
       "      <td>应流转债</td>\n",
       "      <td>2025-09-19</td>\n",
       "      <td>2025-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>sh113699</td>\n",
       "      <td>金25转债</td>\n",
       "      <td>2025-09-26</td>\n",
       "      <td>2025-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>sh110099</td>\n",
       "      <td>福能转债</td>\n",
       "      <td>2025-10-13</td>\n",
       "      <td>2025-10-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>sz123259</td>\n",
       "      <td>锦浪转02</td>\n",
       "      <td>2025-10-17</td>\n",
       "      <td>2025-11-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>71 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      symbol   债券简称       申购日期       上市时间\n",
       "0   sz127103   东南转债 2024-01-03 2024-01-24\n",
       "1   sz123237   佳禾转债 2024-01-04 2024-01-24\n",
       "2   sz123238  卡倍转02 2024-01-11 2024-02-01\n",
       "3   sz123239   锋工转债 2024-01-19 2024-02-22\n",
       "4   sz127104   姚记转债 2024-01-25 2024-02-26\n",
       "..       ...    ...        ...        ...\n",
       "66  sz123258  胜蓝转02 2025-08-28 2025-09-10\n",
       "67  sh113697   应流转债 2025-09-19 2025-10-22\n",
       "68  sh113699  金25转债 2025-09-26 2025-10-27\n",
       "69  sh110099   福能转债 2025-10-13 2025-10-30\n",
       "70  sz123259  锦浪转02 2025-10-17 2025-11-06\n",
       "\n",
       "[71 rows x 4 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "today = pd.Timestamp(datetime.now().date())\n",
    "\n",
    "# 筛选条件：上市时间不为空且不晚于今天\n",
    "filtered_df = df[\n",
    "    df['上市时间'].notna() & \n",
    "    (df['上市时间'] <= today)\n",
    "]\n",
    "filtered_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce03aafe-266a-48af-afec-543689ea6050",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
